﻿using DAL;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Security.Cryptography;
using System.Data;
using DBUtility;

namespace DAL
{
    public class JointDAL
    {
        /// <summary>
        /// 获取虚采入库单列表
        /// </summary>
        public DataSet GetJoint()
        {
            DataSet ds = new DataSet();
            StringBuilder commandText = new StringBuilder();
            commandText.Append(" select ROW_NUMBER() OVER (ORDER BY PInterID)Num,PInterID, "); 
            commandText.Append(" CONVERT(varchar(100), Pdatetime, 23)Fdate,b.NAME FBillerID "); 
            commandText.Append(" from t_ProductMain left join sys_master b on b.ID = PmaikingID ");
            commandText.Append(" where PStateID=2 order by PInterID ");

            try
            {
                DbHelperSQL.connectionString = System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"];
                ds = DbHelperSQL.Query(commandText.ToString());
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return ds;
        }
        /// <summary>
        /// 获取虚采入库单里的明细
        /// </summary>
        public DataSet GetJointDetail(string PInterID)
        {
            DataSet ds = new DataSet();
            StringBuilder commandText = new StringBuilder();
            commandText.Append(" select pid,PkgsBarCode,a.PlaceBarCode,a.FName PlaceName ");
            commandText.Append(" from t_Productinsert left join t_StockPlace a on a.FSPID = PFSPID ");
            commandText.Append(" where PInterID = '" + PInterID + "'");

            try
            {
                DbHelperSQL.connectionString = System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"];
                ds = DbHelperSQL.Query(commandText.ToString());
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return ds;
        }
        /// <summary>
        /// 更新明细库位信息
        /// </summary>
        public bool UpdateJointDetail(string pid, string PkgsBarCode, string PlaceBarCode)
        {
            bool lb_return = false;
            string stockID = ""; string PosiID = "";
            

            lb_return = GetStockPosi(PlaceBarCode, out stockID, out PosiID);

            if (lb_return)
            {
                StringBuilder commandText = new StringBuilder();
                commandText.Append(" update t_Productinsert set ");
                commandText.Append(" PkgsBarCode='" + PkgsBarCode + "'");
                commandText.Append(" ,PStorehouseID='" + stockID + "'");
                commandText.Append(" ,PFSPID='" + PosiID + "'");
                commandText.Append(" where pid='" + pid + "'");
                
                try
                {
                    DbHelperSQL.connectionString = System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"];
                    int i = DbHelperSQL.ExecuteSql(commandText.ToString());
                    if (i > 0)
                    { lb_return = true; }
                    else { lb_return = false; }
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }

            return lb_return;
        }
        //获取仓库  库位
        public bool GetStockPosi(string PlaceBarCode, out string stockID, out string PosiID)
        {
            bool lb_return = false;
            stockID = ""; PosiID = "";
            DataSet dt = new DataSet();
            StringBuilder sql = new StringBuilder();
            sql.Append(" select FParentID,FSPID from t_StockPlace where PlaceBarCode = '" + PlaceBarCode + "'");
            try
            {
                DbHelperSQL.connectionString = System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"];
                dt = DbHelperSQL.Query(sql.ToString());
            }
            catch (Exception ex)
            {
                throw ex;
            }
            if (dt != null)
            {
                lb_return = true;

                foreach (DataRow dr in dt.Tables[0].Rows)
                {
                    stockID = dr[0].ToString().Trim();
                    PosiID = dr[1].ToString().Trim();
                }
            }

            return lb_return;
        }
        /// <summary>
        /// 验收采购单
        /// </summary>
        /// <returns></returns>
        public bool ExamineacceptanceState(string fInterID, string masterid, string accname)
        {
            bool lb_return = false;
            List<string> sql = new List<string>();
            DataSet kcset = GetProductDetailedrk(fInterID);
            //采购单细表
            if (kcset != null)
            {
                foreach (DataTable dt in kcset.Tables)   //遍历所有的datatable
                {
                    foreach (DataRow dr in dt.Rows)
                    {
                        string ItemSku = dr["PSKU"].ToString().Trim();//sku码
                        //string Stick = dr["Pnumber"].ToString().Trim();//每件(根)
                        //string FQty = dr["Pnumber"].ToString().Trim();//总长
                        string StockID = dr["PdStorehouseID"].ToString().Trim();//仓库
                        //string PosiID = dr["PdFSPID"].ToString().Trim();//仓位
                        //string Floor = dr["pdFlayer"].ToString().Trim();//层
                        //string Pbox = dr["Pbox"].ToString().Trim();//箱数
                        string Pzcount = dr["Pcount"].ToString().Trim();//个数
                        //string Pcount = dr["Pcount"].ToString().Trim();//个
                        string InStkTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");//入库时间
                        string InStkMan = accname;//入库人
                        string PkgsStatus = "0";//状态，0可用库存、1锁库、2销售、3拆包、4报损
                        string PurchaseBillNo = dr["ProNo"].ToString().Trim();//入库单号
                        StringBuilder commandText = new StringBuilder();
                        commandText.Append(" insert into stk_Package(PurchaseBillNo,ItemSku,StockID,");
                        commandText.Append(" InStkTime,InStkMan,PkgsStatus,Pzcount)values(");
                        commandText.Append("'" + PurchaseBillNo + "',");
                        commandText.Append("'" + ItemSku + "',");
                        //commandText.Append("'" + Stick + "',");
                        //commandText.Append("'" + FQty + "',"); 
                        commandText.Append("'" + StockID + "',");
                        //commandText.Append("'" + PosiID + "',"); 
                        //commandText.Append("'" + Floor + "',");
                        commandText.Append("'" + InStkTime + "',");
                        commandText.Append("'" + InStkMan + "',");
                        commandText.Append("'" + PkgsStatus + "',");
                        //commandText.Append("'" + Pbox + "',");
                        commandText.Append("'" + Pzcount + "')");
                        //commandText.Append("'" + Pcount + "')");
                        sql.Add(commandText.ToString());
                    }
                }
            }

            sql.Add("update t_ProductMain set PacceptanceState='2',PacceptanceID=" + masterid + " where PInterID = '" + fInterID + "'");

            try
            {
                int i = 0;
                i = DbHelperSQL.ExecuteSqlTran(sql);
                if (i > 0)
                { lb_return = true; }
                else
                { lb_return = false; }

            }
            catch (Exception ex)
            {
                throw ex;
            }
            return lb_return;
        }
        /// 获取单条采购单
        public DataSet GetProductDetailedrk(string PInterID)
        {
            DataSet dt = new DataSet();
            StringBuilder commandText = new StringBuilder();
            commandText.Append(" select PInterID,p.ProNo,PSKU,Pnumber as Pbox,PdStorehouseID,PdFSPID,pdFlayer,d.pnumg as Pcount,p.Pnumg as Pzcount from t_Productinsert p join t_ProductDetailed d on (p.ProNo=d.ProNo) ");
            commandText.Append(" where PInterID='" + PInterID + "' and Pnumber <> 0 and PdStorehouseID <> 0");
            try
            {
                dt = DbHelperSQL.Query(commandText.ToString());

            }
            catch (Exception ex)
            {
                throw ex;
            }
            return dt;
        }

    }
}
